﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Web.Http;
using NPOI.SS.Formula;  // 包含IFormulaEvaluator

//  https://localhost:44334/api/ncgenerator/generate?fileName=%E5%89%AF%E6%9C%AC%E4%BE%A7%E6%9D%BF%E5%85%AC%E5%BC%8F-6.xlsx

public class NcFileResponse
{
    public string FileName { get; set; }
    public byte[] FileContent { get; set; }
}

[RoutePrefix("api/ncgenerator")]
public class NCGeneratorController : ApiController
{
    [HttpGet]
    [Route("generate")]
    public IHttpActionResult GenerateNCFiles(string fileName)
    {
        try
        {
            // 参数校验
            if (string.IsNullOrWhiteSpace(fileName))
            {
                return Content(HttpStatusCode.BadRequest, new { code = 400, msg = "文件名不能为空" });
            }

            // 构建完整文件路径
            var basePath = @"C:\";
            var excelFilePath = Path.Combine(basePath, fileName.Trim());

            // 验证文件存在性
            if (!File.Exists(excelFilePath))
            {
                return Content(HttpStatusCode.NotFound, new
                {
                    code = 404,
                    msg = "文件不存在",
                    detail = $"路径: {excelFilePath}"
                });
            }

            // 验证文件类型
            if (Path.GetExtension(excelFilePath).ToLower() != ".xlsx")
            {
                return Content(HttpStatusCode.BadRequest, new { code = 400, msg = "仅支持.xlsx文件" });
            }

            // 处理Excel文件
            var ncFiles = ProcessExcel(new FileInfo(excelFilePath));

            // 构建响应
            var response = new HttpResponseMessage(HttpStatusCode.OK);
            var content = new MultipartContent("mixed");

            foreach (var ncFile in ncFiles)
            {
                if (ncFile == null) continue;

                var fileContent = new ByteArrayContent(ncFile.FileContent);
                fileContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/octet-stream");
                fileContent.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
                {
                    FileName = Uri.EscapeDataString(ncFile.FileName)
                };
                content.Add(fileContent);
            }

            response.Content = content;
            return ResponseMessage(response);
        }
        catch (Exception ex)
        {
            return Content(HttpStatusCode.InternalServerError, new
            {
                code = 500,
                msg = "处理失败",
                error = ex.Message
            });
        }
    }

    private List<NcFileResponse> ProcessExcel(FileInfo excelFile)
    {
        var result = new List<NcFileResponse>();

        using (var fs = new FileStream(excelFile.FullName, FileMode.Open, FileAccess.Read))
        {
            var workbook = new XSSFWorkbook(fs);
            var worksheet = workbook.GetSheet("Sheet1");

            if (worksheet == null) throw new Exception("Sheet1不存在");

            var columnD = new List<string>();
            var columnE = new List<string>();

            // 数据从第2行开始（索引1）
            for (int rowIdx = 1; rowIdx <= worksheet.LastRowNum; rowIdx++)
            {
                var row = worksheet.GetRow(rowIdx);
                if (row == null) continue;

                // D列（索引3）、E列（索引4）
                AddCellValue(row, 3, columnD);
                AddCellValue(row, 4, columnE);
            }

            result.Add(CreateNcFileResponse(columnD, "一道", excelFile.Name));
            result.Add(CreateNcFileResponse(columnE, "二道", excelFile.Name));
        }

        return result;
    }

    private void AddCellValue(IRow row, int colIdx, List<string> collection)
    {
        var cell = row.GetCell(colIdx);
        if (cell == null) return;

        var value = GetCellValue(cell);
        if (!string.IsNullOrWhiteSpace(value))
        {
            collection.Add(value.Trim());
        }
    }

    private string GetCellValue(ICell cell)
    {
        switch (cell.CellType)
        {
            case CellType.String:
                return cell.StringCellValue;
            case CellType.Numeric:
                return cell.NumericCellValue.ToString("G");
            case CellType.Formula:
                return GetFormulaValue(cell);
            default:
                return cell.ToString().Trim();
        }
    }
    private string GetFormulaValue(ICell cell)
    {
        try
        {
            // 修正后的公式计算器获取方式
            IFormulaEvaluator evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
            var evaluatedCell = evaluator.Evaluate(cell);

            switch (evaluatedCell.CellType)
            {
                case CellType.String:
                    return evaluatedCell.StringValue;
                case CellType.Numeric:
                    return evaluatedCell.NumberValue.ToString("G");
                default:
                    return cell.ToString().Trim();
            }
        }
        catch
        {
            return cell.ToString().Trim();
        }
    }

    private NcFileResponse CreateNcFileResponse(List<string> lines, string suffix, string originalFileName)
    {
        if (lines.Count == 0) return null;

        var baseName = Path.GetFileNameWithoutExtension(originalFileName);
        var fileName = $"{baseName}_{suffix}.nc";

        using (var ms = new MemoryStream())
        using (var writer = new StreamWriter(ms, System.Text.Encoding.ASCII))
        {
            foreach (var line in lines)
            {
                writer.WriteLine(line);
            }
            writer.Flush();

            return new NcFileResponse
            {
                FileName = fileName,
                FileContent = ms.ToArray()
            };
        }
    }
}